DISCLAIMER: This worksheet is provided as a guide only. There is no guarantee or warranty of accuracy. It is based on tax laws as of March 1988. Tax laws change frequently. Consult your tax advisor for current depreciation laws and for current news on the deductibility of interest.
***
This worksheet assumes one mortgage. The amount is entered in cell B3. Cell D3 will show 80% of the total purchase price (B3*.8) as the building value; if this assumption is incorrect, either change the decimal in the formula or delete the formula and enter the value. The value in D3 is used later in the worksheet to determine depreciation.
Cell B4 contains the mortgage amount; if your lender will lend a stated percentage of the purchase price, use a formula in this cell (e.g. , B3*.8, if the lender will finance 80% of the purchase price). Using a formula makes it easier to play around with purchase price variations.
Cells B7 and B8 are locked. They contain formulas to give the interest (stated as an annual amount in B5) as a monthly figure and the term stated in years in B6 as months. The monthly payment is then determined in B9 using the PMT(x,y,z) function of Jazz.
The amortization schedule begins at B13. The number shown for each month is the principal outstanding as of that month. As shown, the purchase is assumed to be made as of July 1. The formula in cell B20 should be copied to the cell corresponding to the actual assumed month of purchase and the remaining cells for the first year should contain the formula in cell B21. NOTE carefully that the formula for the first month of succeeding years is different from the formulas for succeeding months of each year. The formula for the first month of each year picks up from the last cell of a prior year, so that reference is different. Cells in succeeding months of each year refer to the preceding cell. There is a summary of the total principal and interest paid for each year beginning at cell B29.
If the lease is a triple net lease, operating expenses will be picked up by the tenants. If not, the items suggested will get you started on some of the expenses which the owner will incur on a piece of property. Note that no provision is made here for replacement. You will want to consider how much should be put aside for repairs and replacement each month; this amount is not deductible (and so not included), but the prospective owner should be aware of the prudence of such a reserve and that it lessens the amount of the available cash flow.
Depreciation, in row 63, is figured on a straight line basis for the amount shown in cell D3. For nonresidential rental property this is 31.5 years, so for the first full year of ownership and thereafter, D3 is divided by 31.5. For the first year, pro-rate the full year's depreciation by the number of months remaining in that year. In this example, we have half the full year's depreciation available. Depreciation for residential rental property is 27.5 years.
The Debt Service in row 71 is the sum of the principal and interest payments for each year, which is picked up from the summary of the amortization table beginning at cell A27.
Whenever you work on a spreadsheet, put your initials in one of the cells, so you will know the printout is your work product. You will end up with many copies of the printout laying around, and you will want to know which one is the latest; the worksheet is set up so that NOW is printed to help you keep track of the different revisions. NOW is a Jazz function that returns the serial number for the present date and time. In the Mortgage Worksheet, NOW is entered in two cells, E1 and D41.
These cells are in two named ranges, "Amortization" and "Cash Flow." These two ranges can be printed separately by name, so that you don't end up printing amortization schedules over several pages when all you're interested in is the cash flow of the project. To print the range "Cash Flow" (or any other named range), double click in the selection box (you know where that is--you read the manual, right?), it will turn black, and type in the name of the range you wish to print. That range will then turn black in the worksheet, choose Print Selection... from the File menu, and away you go! In either range, NOW will print the serial number for the date, so you can keep track of the printouts.
If you have any questions or comments, feel free to leave me a note on GEnie. My address is STRIPLING.
